package com.dhcc.DRGsHosp.application.impl;

import com.dhcc.DRGsHosp.application.FeeAnalysisDepApplication;
import com.dhcc.DRGsHosp.core.domain.IndexCal;
import com.dhcc.DRGsHosp.infra.util.Arith;
import com.dhcc.DRGsHosp.infra.util.DrgsIndex;
import com.dhcc.DRGsHosp.infra.util.StringUtils;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.dao.DataAccessException;

import javax.inject.Named;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 科室费用分析
 * @author shaozhiyuan
 */
@Named
//@Transactional
public class FeeAnalysisDepApplicationImpl implements FeeAnalysisDepApplication {

	@Autowired
    private JdbcTemplateObj jdbcTemplateObj;

	/**
	 * 科室费用分析趋势
	 */
	@Override
	@Cacheable(value = "drgscom3.1.1", key = "'getDepFeeVareTrend'+ #dateFrom + #dateTo +#depCode +#hospCode +#pType +#groupCode")
	public List<Map<String, Object>> getDepFeeVareTrend(String dateFrom, String dateTo, String depCode, String hospCode,String pType, String groupCode) {
		//二级科室页面-费用变化趋势模块，左右两个图形中应该显示查询时间段内，最大月份的近12个月的趋势图
		int lastYear = Integer.parseInt(dateTo.substring(0,4))-1;//去年
		String lastData=lastYear+dateTo.substring(4); //去年的时间
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select to_char(ipinfo.p00290000,'yyyy-mm') months , \n ");
		sql.append(" round(decode(count(a.pmainindex),0,0,sum(expsum.drg_med_cost)/count(a.pmainindex)),2)  as MedFee, \n ");
		sql.append(" round(decode(count(a.pmainindex),0,0,sum(expsum.drg_nurs_cost)/count(a.pmainindex)),2)  as NursFee, \n ");
		sql.append(" round(decode(count(a.pmainindex),0,0,sum(expsum.drg_tech_cost)/count(a.pmainindex)),2)  as TechFee,\n ");
		sql.append(" round(decode(count(a.pmainindex),0,0,sum(expsum.drg_drug_cost)/count(a.pmainindex)),2)  as DrugMaterFee,\n ");
		sql.append(" round(decode(count(a.pmainindex),0,0,sum(expsum.drg_manage_cost)/count(a.pmainindex)),2)  as ManageFee\n ");
		sql.append(" from DRGs.Mr_Baseinfo a  left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex   \n ");
		sql.append(" left join drgs.mr_expense_sum expsum on  expsum.pmainindex=a.pmainindex \n ");
		sql.append(" left join DRGs.t_department dep on ipinfo.P00300001=dep.dep_code \n ");
		sql.append(" LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ipinfo.PMAININDEX \n ");
		sql.append(" where ipinfo.P00300001='"+depCode+"'  \n ");
		sql.append(" and ipinfo.P00290000>to_date('"+lastData+"','yyyy-mm-dd') and ipinfo.P00290000<=to_date('"+dateTo+"','yyyy-mm-dd')  \n ");
		//***医院编码***
		if(StringUtils.hasLength(hospCode)){
			sql.append(" and instr('"+hospCode+"', a.p00010001)>0   \n" ) ;
		}
		//***主诊组编码***
		if(StringUtils.hasLength(groupCode)){
			sql.append(" and ipinfo.P00601001='"+groupCode+"'  \n" ) ;
		}
		//***医保条件***
		if("1".equals(pType)){
			sql.append(" AND  medins.parid is not null  \n ");
		}
		sql.append(" group by to_char(ipinfo.p00290000,'yyyy-mm') order by to_char(ipinfo.p00290000,'yyyy-mm') \n ");
		
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 不同特征区间内病例分布情况
	 */
	@Override
	@Cacheable(value = "drgscom3.1.1", key = "'getDepOverFeeCaseShow'+ #dateFrom + #dateTo +#depCode +#hospCode"
			+ " +#sliderValBench +#compareObj +#compareType +#groupScheme +#stdId +#pType +#groupCode")
	public List<Map<String, Object>> getDepOverFeeCaseShow(String dateFrom, String dateTo, String depCode,
			String hospCode, String sliderValBench, String compareObj, String compareType, String groupScheme,
			String stdId,String pType, String groupCode) {
		StringBuilder sql = new StringBuilder();
		String tip=""; //指标项
		
		//标杆字段
		if("AvgDays".equals(compareObj)){
			compareObj="AS_AVG_DAYS";
			tip="ipinfo.P00320000";
		}else if("AvgFee".equals(compareObj)){
			compareObj="AS_AVG_FEE";
			tip="ipinfo.P00800000";
		}else if("MedFee".equals(compareObj)){
			compareObj="AS_DRG_MED_COST";
			tip="expsum.DRG_MED_COST";
		}else if("TechFee".equals(compareObj)){
			compareObj="AS_DRG_TECH_COST";
			tip="expsum.DRG_TECH_COST";
		}else if("NursFee".equals(compareObj)){
			compareObj="AS_DRG_NURS_COST";
			tip="expsum.DRG_NURS_COST";
		}else if("DrugMaterFee".equals(compareObj)){
			compareObj="AS_DRG_DRUG_COST";
			tip="expsum.DRG_DRUG_COST";
		}else if("ManageFee".equals(compareObj)){
			compareObj="AS_DRG_MANAGE_COST";
			tip="expsum.DRG_MANAGE_COST";
		}
		
		//划分类型 1是标杆法区间划分 2是标差法区间划分
		if("1".equals(compareType)){
			sql.setLength(0);
			sql.append(" select ipinfo.P00601000 DECC,dp.group_code GCODE , count(1) CNT, \n" ) ;
			sql.append(" sum (case when "+tip+"<=ad."+compareObj+"  then 1 else 0 end  )  NOTCNT, \n" ) ;
			sql.append(" sum (case when ad."+compareObj+"<"+tip+" and "+tip+"<="+sliderValBench+"*ad."+compareObj+"  then 1 else 0 end  )  INCNT, \n" ) ;
			sql.append(" sum (case when "+tip+">"+sliderValBench+"*ad."+compareObj+"  then 1 else 0 end  )  MORECNT \n" ) ;
			sql.append(" from drgs.mr_baseinfo a  \n" ) ;
			sql.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n" ) ;
			sql.append(" left join drgs.t_department dt on dt.dep_code=ipinfo.P00300001 \n" ) ;
			sql.append(" LEFT join drgs.t_clinicgroup dp on dp.group_code=ipinfo.P00601001 \n" ) ;
			sql.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid  \n" ) ;
			sql.append(" left join drgs.t_annualstd ad on ad.as_drg_code=pd.pardrg_code \n" ) ;
			sql.append(" left join drgs.mr_expense_sum expsum on ipinfo.pmainindex=expsum.pmainindex \n" ) ;
			sql.append(" LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ipinfo.PMAININDEX \n ");
			sql.append(" where pd.ver_region='"+groupScheme+"' and  ipinfo.P00300001='"+depCode+"' \n" ) ;
			sql.append(" and ad.as_id='"+stdId+"' "+Arith.mrOutDateSql(dateFrom,dateTo,"ipinfo",1)+"  \n" ) ;
			//***医院编码***
			if(StringUtils.hasLength(hospCode)){
				sql.append(" and instr('"+hospCode+"', a.p00010001)>0   \n" ) ;
			}
			//***主诊组编码***
			if(StringUtils.hasLength(groupCode)){
				sql.append(" and ipinfo.P00601001='"+groupCode+"'  \n" ) ;
			}
			//***医保条件***
			if("1".equals(pType)){
				sql.append(" AND  medins.parid is not null  \n ");
			}
			sql.append(" group by  ipinfo.P00601000 ,dp.group_code \n ");
		}else{
			sql.setLength(0);
			sql.append(" select ipinfo.P00601000 DECC,dp.group_code GCODE , count(1) CNT, \n" ) ;
			sql.append(" sum (case when "+tip+"<=stddevia.stddevia then 1 else 0 end  )  NOTCNT, \n" ) ;
			sql.append(" sum (case when stddevia.stddevia < "+tip+" and "+tip+"<= stddevia.stdd then 1 else 0 end  )  INCNT, \n" ) ;
			sql.append(" sum (case when "+tip+" > stddevia.stdd  then 1 else 0 end  )  MORECNT \n" ) ;
			sql.append(" from drgs.mr_baseinfo a  \n" ) ;
			sql.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n" ) ;
			sql.append(" left join drgs.t_department dt on dt.dep_code=ipinfo.P00300001 \n" ) ;
			sql.append(" LEFT join drgs.t_clinicgroup dp on dp.group_code=ipinfo.P00601001 \n" ) ;
			sql.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid  \n" ) ;
			sql.append(" left join drgs.t_annualstd ad on ad.as_drg_code=pd.pardrg_code \n" ) ;
			sql.append(" left join drgs.mr_expense_sum expsum on ipinfo.pmainindex=expsum.pmainindex \n" ) ;
			sql.append(" LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ipinfo.PMAININDEX \n ");
			sql.append(" left join ( \n" ) ;
			sql.append(" select  pd.pardrg_code, avg("+tip+") stddevia ,(1+"+sliderValBench+"*stddev("+tip+")/100)* avg("+tip+") stdd \n" ) ;
			sql.append(" from drgs.mr_baseinfo a   left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex  \n" ) ;
			sql.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid   \n" ) ;
			sql.append(" left join drgs.t_annualstd ad on ad.as_drg_code=pd.pardrg_code  \n" ) ;
			sql.append(" LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ipinfo.PMAININDEX \n ");
			sql.append(" left join drgs.mr_expense_sum expsum on ipinfo.pmainindex=expsum.pmainindex  \n" ) ;
			sql.append(" where pd.ver_region='"+groupScheme+"' and  ipinfo.P00300001='"+depCode+"'  \n" ) ;
			sql.append(" and ad.as_id='"+stdId+"' "+Arith.mrOutDateSql(dateFrom,dateTo,"ipinfo",1)+" \n" ) ;
			//***医院编码***
			if(StringUtils.hasLength(hospCode)){
				sql.append(" and instr('"+hospCode+"', a.p00010001)>0   \n" ) ;
			}
			//***主诊组编码***
			if(StringUtils.hasLength(groupCode)){
				sql.append(" and ipinfo.P00601001='"+groupCode+"'  \n" ) ;
			}
			//***医保条件***
			if("1".equals(pType)){
				sql.append(" AND  medins.parid is not null  \n ");
			}
			sql.append(" group by pd.pardrg_code ) stddevia on stddevia.pardrg_code=pd.pardrg_code \n" ) ;
			sql.append(" where pd.ver_region='"+groupScheme+"' and  ipinfo.P00300001='"+depCode+"' \n" ) ;
			sql.append(" and ad.as_id='"+stdId+"' "+Arith.mrOutDateSql(dateFrom,dateTo,"ipinfo",1)+"  \n" ) ;
			//***医院编码***
			if(StringUtils.hasLength(hospCode)){
				sql.append(" and instr('"+hospCode+"', a.p00010001)>0   \n" ) ;
			}
			//***主诊组编码***
			if(StringUtils.hasLength(groupCode)){
				sql.append(" and ipinfo.P00601001='"+groupCode+"'  \n" ) ;
			}
			//***医保条件***
			if("1".equals(pType)){
				sql.append(" AND  medins.parid is not null  \n ");
			}
			sql.append(" group by  ipinfo.P00601000 ,dp.group_code \n ");
		}
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 排除特异病例后次均费用.费用构成及其对比情况表
	 */
	@Override
	@Cacheable(value = "drgscom3.1.1", key = "'getDepFeeOutAbnormalTable'+ #dateFrom + #dateTo +#depCode +#hospCode"
			+ " +#sliderValBench +#compareObj +#compareType +#groupScheme +#stdId +#compareAbnormal +#pType +#groupCode")
	public List<Map<String, Object>> getDepFeeOutAbnormalTable(String dateFrom, String dateTo, String depCode,
			String hospCode, String sliderValBench, String compareObj, String compareType, String groupScheme,
			String stdId,String compareAbnormal, String pType, String groupCode) {
		
				StringBuilder sql = new StringBuilder();
				String tip=""; //指标项
				
				//标杆字段
				if("AvgDays".equals(compareObj)){
					compareObj="AS_AVG_DAYS";
					tip="ipinfo.P00320000";
				}else if("AvgFee".equals(compareObj)){
					compareObj="AS_AVG_FEE";
					tip="ipinfo.P00800000";
				}else if("MedFee".equals(compareObj)){
					compareObj="AS_DRG_MED_COST";
					tip="expsum.DRG_MED_COST";
				}else if("TechFee".equals(compareObj)){
					compareObj="AS_DRG_TECH_COST";
					tip="expsum.DRG_TECH_COST";
				}else if("NursFee".equals(compareObj)){
					compareObj="AS_DRG_NURS_COST";
					tip="expsum.DRG_NURS_COST";
				}else if("DrugMaterFee".equals(compareObj)){
					compareObj="AS_DRG_DRUG_COST";
					tip="expsum.DRG_DRUG_COST";
				}else if("ManageFee".equals(compareObj)){
					compareObj="AS_DRG_MANAGE_COST";
					tip="expsum.DRG_MANAGE_COST";
				}
				
				//绩效年份选择时间对应的年份，如果没有选择系统最大
				String managerSql=""; //设置绩效值sql
				String managerYearSql=" select count(1) from drgs.T_QUOTAMANAGE t where t.par_ms_code like 'manager_%' AND t.manage_year='"+dateTo.substring(0,4)+"' ";
				if(jdbcTemplateObj.queryForObject(managerYearSql.toString(), Integer.class)>1){
					managerSql=" from drgs.T_QUOTAMANAGE t where t.par_ms_code like 'manager_%' and rownum<2 and t.manage_year='"+dateTo.substring(0,4)+"'  \n ";
					managerSql += " and t.pardep_code='"+depCode+"' and t.patient_type='"+pType+"' and t.pardrg_code is null \n ";
				}else{
					managerSql=" from drgs.T_QUOTAMANAGE t where t.par_ms_code like 'manager_%' and rownum<2  \n ";
					managerSql += " and t.pardep_code='"+depCode+"' and t.patient_type='"+pType+"' and t.pardrg_code is null \n ";
					managerSql += " and t.manage_year in ( select max(t.manage_year) from drgs.T_QUOTAMANAGE t where t.par_ms_code like 'manager_%' )   \n ";
				}
				
				sql.setLength(0);
				sql.append(" (select '本期值' DECC,  count(1) cnt,round(decode(count(1),0,0,sum(ipinfo.P00800000)/count(1)),2) AvgFee  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(ipinfo.P00320000)/count(1)),2) AvgDays  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(expsum.drug_cost)/count(1)),2) AvgDrug  \n ");
				sql.append(" ,round(decode(sum(ipinfo.P00800000),0,0,sum(expsum.drug_cost)/sum(ipinfo.P00800000))*100,2) AvgDrugRATIO  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(expsum.mater_cost)/count(1)),2) AvgMater  \n ");
				sql.append(" ,round(decode(sum(ipinfo.P00320000),0,0,sum(expsum.mater_cost)/sum(ipinfo.P00320000))*100,2) AvgMATERRATIO  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Med_Cost)/count(1)),2) MedFee  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Tech_Cost)/count(1)),2) TechFee  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Nurs_Cost)/count(1)),2) NursFee  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Drug_Cost)/count(1)),2) DrugMaterFee  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Manage_Cost)/count(1)),2) ManageFee  \n ");
				sql.append(" from drgs.mr_baseinfo a \n ");
				sql.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex  \n ");
				sql.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid   \n ");
				sql.append(" left join drgs.mr_expense_sum expsum on ipinfo.pmainindex=expsum.pmainindex  \n ");
				sql.append(" LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ipinfo.PMAININDEX \n ");
				sql.append(" where ipinfo.P00300001='"+depCode+"' and pd.ver_region='"+groupScheme+"' \n ");
				//***医院编码***
				if(StringUtils.hasLength(hospCode)){
					sql.append(" and instr('"+hospCode+"', a.p00010001)>0   \n" ) ;
				}
				//***主诊组编码***
				if(StringUtils.hasLength(groupCode)){
					sql.append(" and ipinfo.P00601001='"+groupCode+"'  \n" ) ;
				}
				//***医保条件***
				if("1".equals(pType)){
					sql.append(" AND  medins.parid is not null  \n ");
				}
				sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"ipinfo",1)+" )  \n");
				sql.append(" UNION  \n ");
				sql.append(" (select '矫正值' DECC, count(1) cnt,round(decode(count(1),0,0,sum(ipinfo.P00800000)/count(1)),2) AvgFee  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(ipinfo.P00320000)/count(1)),2) AvgDays  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(expsum.drug_cost)/count(1)),2) AvgDrug  \n ");
				sql.append(" ,round(decode(sum(ipinfo.P00800000),0,0,sum(expsum.drug_cost)/sum(ipinfo.P00800000))*100,2) AvgDrugRATIO  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(expsum.mater_cost)/count(1)),2) AvgMater  \n ");
				sql.append(" ,round(decode(sum(ipinfo.P00320000),0,0,sum(expsum.mater_cost)/sum(ipinfo.P00320000))*100,2) AvgMATERRATIO  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Med_Cost)/count(1)),2) MedFee  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Tech_Cost)/count(1)),2) TechFee  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Nurs_Cost)/count(1)),2) NursFee  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Drug_Cost)/count(1)),2) DrugMaterFee  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Manage_Cost)/count(1)),2) ManageFee  \n ");
				sql.append(" from drgs.mr_baseinfo a  left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex   \n ");
				sql.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid   \n ");
				sql.append(" left join drgs.mr_expense_sum expsum on ipinfo.pmainindex=expsum.pmainindex  \n ");
				sql.append(" left join drgs.t_annualstd ad on ad.as_drg_code=pd.pardrg_code  \n ");
				sql.append(" LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ipinfo.PMAININDEX \n ");
				if("2".equals(compareType)){
				sql.append(" left join ( \n" ) ;
				sql.append(" select  pd.pardrg_code, avg("+tip+") stddevia ,(1+"+sliderValBench+"*stddev("+tip+")/100)* avg("+tip+") stdd \n" ) ;
				sql.append(" from drgs.mr_baseinfo a   left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex  \n" ) ;
				sql.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid   \n" ) ;
				sql.append(" left join drgs.t_annualstd ad on ad.as_drg_code=pd.pardrg_code  \n" ) ;
				sql.append(" LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ipinfo.PMAININDEX \n ");
				sql.append(" left join drgs.mr_expense_sum expsum on ipinfo.pmainindex=expsum.pmainindex  \n" ) ;
				sql.append(" where pd.ver_region='"+groupScheme+"' and  ipinfo.P00300001='"+depCode+"'  \n" ) ;
				sql.append(" and ad.as_id='"+stdId+"' "+Arith.mrOutDateSql(dateFrom,dateTo,"ipinfo",1)+" \n" ) ;
				//***医院编码***
				if(StringUtils.hasLength(hospCode)){
					sql.append(" and instr('"+hospCode+"', a.p00010001)>0   \n" ) ;
				}
				//***主诊组编码***
				if(StringUtils.hasLength(groupCode)){
					sql.append(" and ipinfo.P00601001='"+groupCode+"'  \n" ) ;
				}
				//***医保条件***
				if("1".equals(pType)){
					sql.append(" AND  medins.parid is not null  \n ");
				}
				sql.append(" group by pd.pardrg_code ) stddevia on stddevia.pardrg_code=pd.pardrg_code \n" ) ;
				}
				sql.append(" where  ipinfo.P00300001='"+depCode+"' and ad.as_id='"+stdId+"' and pd.ver_region='"+groupScheme+"' \n ");
				//***医院编码***
				if(StringUtils.hasLength(hospCode)){
					sql.append(" and instr('"+hospCode+"', a.p00010001)>0   \n" ) ;
				}
				//***主诊组编码***
				if(StringUtils.hasLength(groupCode)){
					sql.append(" and ipinfo.P00601001='"+groupCode+"'  \n" ) ;
				}
				//***医保条件***
				if("1".equals(pType)){
					sql.append(" AND  medins.parid is not null  \n ");
				}
				sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"ipinfo",1)+" \n ");
			
				if("1".equals(compareType)){ // 1是标杆法
					if("1".equals(compareAbnormal)){ //1  剔除是超标N%及以上病例
						sql.append(" and "+tip+"<"+sliderValBench+"*ad."+compareObj+" \n" ) ;
					}else{ //2  是 剔除超标N%以内病例
						sql.append(" and ( "+tip+">"+sliderValBench+"*ad."+compareObj+" or ad."+compareObj+">="+tip+" )  \n" ) ;
					}
				}else{   //2是标差法
					if("1".equals(compareAbnormal)){ //1  剔除是超出N倍及以上标准差病例
						sql.append(" and "+tip+"<stddevia.stdd  \n" ) ;
					}else{ //2  是 剔除超出N倍以内标准差病例
						sql.append(" and ( "+tip+">stddevia.stdd  or stddevia.stddevia >="+tip+" )  \n" ) ;
					}
				}
				sql.append(" ) union  \n ");
				sql.append(" ( select '标杆值' DECC,  count(1) cnt,round(decode(count(1),0,0,sum(ad.as_avg_fee)/count(1)),2) AvgFee  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(ad.as_avg_days)/count(1)),2) AvgDays  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(ad.as_avg_drug_fee)/count(1)),2) AvgDrug  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(ad.as_drug_ratio)/count(1)),2) AvgDrugRATIO  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(ad.as_avg_material_fee)/count(1)),2) AvgMater  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(ad.as_material_ratio)/count(1)),2) AvgMATERRATIO  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(ad.as_drg_med_cost)/count(1)),2) MedFee  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(ad.as_drg_tech_cost)/count(1)),2) TechFee  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(ad.as_drg_nurs_cost)/count(1)),2) NursFee  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(ad.as_drg_drug_cost)/count(1)),2) DrugMaterFee  \n ");
				sql.append(" ,round(decode(count(1),0,0,sum(ad.as_drg_manage_cost)/count(1)),2) ManageFee  \n ");
				sql.append(" from drgs.mr_baseinfo a   \n ");
				sql.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex  \n ");
				sql.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid   \n ");
				sql.append(" left join drgs.mr_expense_sum expsum on ipinfo.pmainindex=expsum.pmainindex   \n ");
				sql.append(" left join drgs.t_annualstd ad on ad.as_drg_code=pd.pardrg_code  \n ");
				sql.append(" LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ipinfo.PMAININDEX \n ");
				sql.append(" where ipinfo.P00300001='"+depCode+"' and ad.as_id='"+stdId+"' and pd.ver_region='"+groupScheme+"'  \n ");
				//***医院编码***
				if(StringUtils.hasLength(hospCode)){
					sql.append(" and instr('"+hospCode+"', a.p00010001)>0   \n" ) ;
				}
				//***主诊组编码***
				if(StringUtils.hasLength(groupCode)){
					sql.append(" and ipinfo.P00601001='"+groupCode+"'  \n" ) ;
				}
				//***医保条件***
				if("1".equals(pType)){
					sql.append(" AND  medins.parid is not null  \n ");
				}
				sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"ipinfo",1)+"  \n ");
				sql.append(" ) union ( select '绩效值' DECC,  0 cnt , (select  t.manage_benchmark  \n ");
				sql.append( managerSql );
				sql.append(" and t.parquota_code='AvgFee'  ) AvgFee ,(select  t.manage_benchmark  \n ");
				sql.append( managerSql );
				sql.append(" and t.parquota_code='AvgDays' )AvgDays , (select  t.manage_benchmark  \n ");
				sql.append( managerSql );
				sql.append(" and t.parquota_code='AvgDrugFee' ) AvgDrug , (select  t.manage_benchmark   \n ");
				sql.append( managerSql );
				sql.append(" and t.parquota_code='DrugRatio' ) AvgDrugRATIO , (select  t.manage_benchmark  \n ");
				sql.append( managerSql );
				sql.append(" and t.parquota_code='AvgMateFee' ) AvgMater, (select  t.manage_benchmark \n ");
				sql.append( managerSql );
				sql.append(" and t.parquota_code='MaterialRatio' ) AvgMATERRATIO, 0 MedFee, 0 TechFee , 0 NursFee, 0 DrugMaterFee, 0 ManageFee from dual  )  \n ");
				List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
				return list;
	}
	/**
	 * 排除特异病例后次均费用.费用构成及其对比情况表(主诊组维度)
	 */
	@Override
	@Cacheable(value = "drgscom3.1.1", key = "'getDepFeeOutAbnormalZZZ'+ #dateFrom + #dateTo +#depCode +#hospCode"
			+ " +#sliderValBench +#compareTip +#compareObj +#compareType +#groupScheme +#stdId +#compareAbnormal +#pType")
	public List<Map<String, Object>> getDepFeeOutAbnormalZZZ(String dateFrom, String dateTo, String depCode,
			String hospCode, String sliderValBench, String compareTip, String compareObj, String compareType,
			String groupScheme, String stdId, String compareAbnormal, String pType) {
		
		StringBuilder sql = new StringBuilder();
		String tip=""; //指标项
		
		//标杆字段
		if("AvgDays".equals(compareObj)){
			compareObj="AS_AVG_DAYS";
			tip="ipinfo.P00320000";
		}else if("AvgFee".equals(compareObj)){
			compareObj="AS_AVG_FEE";
			tip="ipinfo.P00800000";
		}else if("MedFee".equals(compareObj)){
			compareObj="AS_DRG_MED_COST";
			tip="expsum.DRG_MED_COST";
		}else if("TechFee".equals(compareObj)){
			compareObj="AS_DRG_TECH_COST";
			tip="expsum.DRG_TECH_COST";
		}else if("NursFee".equals(compareObj)){
			compareObj="AS_DRG_NURS_COST";
			tip="expsum.DRG_NURS_COST";
		}else if("DrugMaterFee".equals(compareObj)){
			compareObj="AS_DRG_DRUG_COST";
			tip="expsum.DRG_DRUG_COST";
		}else if("ManageFee".equals(compareObj)){
			compareObj="AS_DRG_MANAGE_COST";
			tip="expsum.DRG_MANAGE_COST";
		}
		
		sql.setLength(0);
		if("本期值".equals(compareTip)){
			sql.append(" select ipinfo.P00601000 DECC,  count(1) cnt,round(decode(count(1),0,0,sum(ipinfo.P00800000)/count(1)),2) AvgFee  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(ipinfo.P00320000)/count(1)),2) AvgDays  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(expsum.drug_cost)/count(1)),2) AvgDrug  \n ");
			sql.append(" ,round(decode(sum(ipinfo.P00800000),0,0,sum(expsum.drug_cost)/sum(ipinfo.P00800000))*100,2) AvgDrugRATIO  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(expsum.mater_cost)/count(1)),2) AvgMater  \n ");
			sql.append(" ,round(decode(sum(ipinfo.P00320000),0,0,sum(expsum.mater_cost)/sum(ipinfo.P00320000))*100,2) AvgMATERRATIO  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Med_Cost)/count(1)),2) MedFee  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Tech_Cost)/count(1)),2) TechFee  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Nurs_Cost)/count(1)),2) NursFee  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Drug_Cost)/count(1)),2) DrugMaterFee  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Manage_Cost)/count(1)),2) ManageFee  \n ");
		}else if("矫正值".equals(compareTip)){
			sql.append(" select ipinfo.P00601000 DECC, count(1) cnt,round(decode(count(1),0,0,sum(ipinfo.P00800000)/count(1)),2) AvgFee  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(ipinfo.P00320000)/count(1)),2) AvgDays  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(expsum.drug_cost)/count(1)),2) AvgDrug  \n ");
			sql.append(" ,round(decode(sum(ipinfo.P00800000),0,0,sum(expsum.drug_cost)/sum(ipinfo.P00800000))*100,2) AvgDrugRATIO  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(expsum.mater_cost)/count(1)),2) AvgMater  \n ");
			sql.append(" ,round(decode(sum(ipinfo.P00320000),0,0,sum(expsum.mater_cost)/sum(ipinfo.P00320000))*100,2) AvgMATERRATIO  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Med_Cost)/count(1)),2) MedFee  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Tech_Cost)/count(1)),2) TechFee  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Nurs_Cost)/count(1)),2) NursFee  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Drug_Cost)/count(1)),2) DrugMaterFee  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Manage_Cost)/count(1)),2) ManageFee  \n ");
		}else if("标杆值".equals(compareTip)){
			sql.append("  select ipinfo.P00601000 DECC,  count(1) cnt,round(decode(count(1),0,0,sum(ad.as_avg_fee)/count(1)),2) AvgFee  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(ad.as_avg_days)/count(1)),2) AvgDays  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(ad.as_avg_drug_fee)/count(1)),2) AvgDrug  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(ad.as_drug_ratio)/count(1)),2) AvgDrugRATIO  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(ad.as_avg_material_fee)/count(1)),2) AvgMater  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(ad.as_material_ratio)/count(1)),2) AvgMATERRATIO  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(ad.as_drg_med_cost)/count(1)),2) MedFee  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(ad.as_drg_tech_cost)/count(1)),2) TechFee  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(ad.as_drg_nurs_cost)/count(1)),2) NursFee  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(ad.as_drg_drug_cost)/count(1)),2) DrugMaterFee  \n ");
			sql.append(" ,round(decode(count(1),0,0,sum(ad.as_drg_manage_cost)/count(1)),2) ManageFee  \n ");
		}else{
			sql.append(" select ipinfo.P00601000 DECC,  count(1) cnt, 0 AvgFee  \n ");
			sql.append(" ,0 AvgDays, 0 AvgDrug,0 AvgDrugRATIO ,0 AvgMater ,0 AvgMATERRATIO ,0  MedFee  \n ");
			sql.append(" ,0 TechFee  \n ");
			sql.append(" ,0 NursFee  \n ");
			sql.append(" ,0 DrugMaterFee  \n ");
			sql.append(" ,0 ManageFee  \n ");
		}
		sql.append(" from drgs.mr_baseinfo a   \n ");
		sql.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex  \n ");
		sql.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid   \n ");
		sql.append(" left join drgs.mr_expense_sum expsum on ipinfo.pmainindex=expsum.pmainindex  \n ");
		sql.append(" left join drgs.t_annualstd ad on ad.as_drg_code=pd.pardrg_code  \n ");
		sql.append(" LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ipinfo.PMAININDEX \n ");
		if("2".equals(compareType)){
		sql.append(" left join ( \n" ) ;
		sql.append(" select  pd.pardrg_code, avg("+tip+") stddevia ,(1+"+sliderValBench+"*stddev("+tip+")/100)* avg("+tip+") stdd \n" ) ;
		sql.append(" from drgs.mr_baseinfo a   left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex  \n" ) ;
		sql.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid   \n" ) ;
		sql.append(" left join drgs.t_annualstd ad on ad.as_drg_code=pd.pardrg_code  \n" ) ;
		sql.append(" LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ipinfo.PMAININDEX \n ");
		sql.append(" left join drgs.mr_expense_sum expsum on ipinfo.pmainindex=expsum.pmainindex  \n" ) ;
		sql.append(" where pd.ver_region='"+groupScheme+"' and  ipinfo.P00300001='"+depCode+"'  \n" ) ;
		sql.append(" and ad.as_id='"+stdId+"' "+Arith.mrOutDateSql(dateFrom,dateTo,"ipinfo",1)+" \n" ) ;
		//***医院编码***
		if(StringUtils.hasLength(hospCode)){
			sql.append(" and instr('"+hospCode+"', a.p00010001)>0   \n" ) ;
		}
		//***医保条件***
		if("1".equals(pType)){
			sql.append(" AND  medins.parid is not null  \n ");
		}
		sql.append(" group by pd.pardrg_code ) stddevia on stddevia.pardrg_code=pd.pardrg_code \n" ) ;
		}
		sql.append(" where ipinfo.P00300001='"+depCode+"' and pd.ver_region='"+groupScheme+"'  \n ");
		//***医院编码***
		if(StringUtils.hasLength(hospCode)){
			sql.append(" and instr('"+hospCode+"', a.p00010001)>0   \n" ) ;
		}
		//***医保条件***
		if("1".equals(pType)){
			sql.append(" AND  medins.parid is not null  \n ");
		}
		if("矫正值".equals(compareTip)){
			if("1".equals(compareType)){ // 1是标杆法
				if("1".equals(compareAbnormal)){ //1  剔除是超标N%及以上病例
					sql.append(" and "+tip+"<"+sliderValBench+"*ad."+compareObj+"   \n" ) ;
				}else{ //2  是 剔除超标N%以内病例
					sql.append(" and ( "+tip+">"+sliderValBench+"*ad."+compareObj+" or ad."+compareObj+">="+tip+" )  \n" ) ;
				}
			}else{   //2是标差法
				if("1".equals(compareAbnormal)){ //1  剔除是超出N倍及以上标准差病例
					sql.append(" and "+tip+"<stddevia.stdd \n" ) ;
				}else{ //2  是 剔除超出N倍以内标准差病例
					sql.append(" and ( "+tip+">stddevia.stdd or stddevia.stddevia>="+tip+" )  \n" ) ;
				}
			}
		}
		sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"ipinfo",1)+" and ad.as_id='"+stdId+"' \n ");
		sql.append(" group by ipinfo.P00601000 order by count(1) desc \n" ) ;
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 科室费用分析病例区间分布的病例数点击下的病例明细页面
	 */
	@Override
	public List<Map<String, Object>> loadFeeAnalysisCaseTable(IndexCal index) {
        String dateFrom = index.getDateFrom()==null?"":index.getDateFrom().toString();
        String dateTo = index.getDateTo()==null?"":index.getDateTo().toString();
        String hospCode = index.getHospCode()==null?"":index.getHospCode().toString();
        String monitorType = index.getMonitorType()==null?"":index.getMonitorType().toString();
        String dimensionCode = index.getPargroupName()==null?"":index.getPargroupName().toString();
        String sliderValBenchLater = index.getType2()==null?"":index.getType2().toString();
        String compareType = index.getCompareType()==null?"":index.getCompareType().toString();
        String compareObj = index.getQuotaCode()==null?"":index.getQuotaCode().toString();
        String abnomalType = index.getType3()==null?"":index.getType3().toString();
        String groupScheme = index.getGroupType()==null?"":index.getGroupType().toString();
        String stdId = index.getStdId()==null?"":index.getStdId().toString();
        String pType = index.getpType()==null?"":index.getpType().toString();
        String depCode = index.getPardepCode()==null?"":index.getPardepCode().toString();
        String drgCode = index.getPardrgCode()==null?"":index.getPardrgCode().toString();
		
		StringBuilder sql = new StringBuilder();
		String tip=""; //指标项
		
		//标杆字段
		if("AvgDays".equals(compareObj)){
			compareObj="AS_AVG_DAYS";
			tip="ipinfo.P00320000";
		}else if("AvgFee".equals(compareObj)){
			compareObj="AS_AVG_FEE";
			tip="ipinfo.P00800000";
		}else if("MedFee".equals(compareObj)){
			compareObj="AS_DRG_MED_COST";
			tip="expsum.DRG_MED_COST";
		}else if("TechFee".equals(compareObj)){
			compareObj="AS_DRG_TECH_COST";
			tip="expsum.DRG_TECH_COST";
		}else if("NursFee".equals(compareObj)){
			compareObj="AS_DRG_NURS_COST";
			tip="expsum.DRG_NURS_COST";
		}else if("DrugMaterFee".equals(compareObj)){
			compareObj="AS_DRG_DRUG_COST";
			tip="expsum.DRG_DRUG_COST";
		}else if("ManageFee".equals(compareObj)){
			compareObj="AS_DRG_MANAGE_COST";
			tip="expsum.DRG_MANAGE_COST";
		}
		
		//判断系统手术表PO1400 判断手术操作字段 是否有区分
		String operSql = "SELECT COUNT(A.PMAININDEX)  FROM DRGS.MR_IPINFO B LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=B.PMAININDEX " ;
		operSql+=" JOIN DRGS.MR_OPERATION A ON A.PMAININDEX = B.PMAININDEX WHERE instr('"+hospCode+"' , BA.p00010001)>0 ";
		operSql+=" and A.PO1400 in ('0','1') and  A.POTYPE='M' "+Arith.mrOutDateSql(dateFrom,dateTo,"B",1)+" ";
		
		sql.setLength(0);
		sql.append(" select a.PMAININDEX PMAININDEX, a.P00050000 MRNO, a.P00060000 NAME, a.P00070001 GENDER, a.P00090000 AGE,  \n ") ; 
		sql.append(" to_char(ipinfo.P00250000,'yyyy-mm-dd') INDATE, to_char(ipinfo.P00290000,'yyyy-mm-dd')  OUTDATE , ipinfo.P00300000 OUTDEP, \n ");
		sql.append(" ipinfo.P00601000 DR, diag.pd0101||'-'||diag.pd0100 diag , operation.PO0101||'-'||operation.PO0100 oper, pd.PARDRG_CODE||'-'||drg.DRG_DESC drgs, ipinfo.P00800000 cost , ad.AS_AVG_FEE  costf, \n ") ;
	    sql.append(" ipinfo.P00320000 indays , ad.AS_AVG_DAYS indaysf , expsum.DRG_MED_COST med, ad.AS_DRG_MED_COST medf , expsum.DRG_NURS_COST nurs, ad.AS_DRG_NURS_COST nursf , \n ") ;
	    sql.append(" expsum.DRG_MANAGE_COST manage  ,ad.AS_DRG_MANAGE_COST managef , expsum.DRG_TECH_COST tech , ad.AS_DRG_TECH_COST techf,  expsum.DRG_DRUG_COST drug , ad.AS_DRG_DRUG_COST drugf  \n ") ;
	    sql.append(" from drgs.mr_baseinfo a \n ");
	    sql.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex \n ");
	    sql.append(" left join drgs.mr_diagnose diag on ipinfo.pmainindex=diag.pmainindex and diag.pdtype='M'  \n ") ;
	    sql.append(" left join drgs.Mr_Operation operation on ipinfo.pmainindex=operation.pmainindex and operation.potype='M'  \n ") ;
	    //手术表做成区分手术或操作字段
	    if(jdbcTemplateObj.queryForObject(operSql.toString(), Integer.class)>1){
	    	sql.append(" and operation.po1400='1' \n ");
		}
	    sql.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid   \n ") ;
	    sql.append(" LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ipinfo.PMAININDEX \n ");
	    sql.append(" left join drgs.t_annualstd ad on ad.as_drg_code=pd.pardrg_code \n" ) ;
	    sql.append(" left join drgs.mr_expense_sum expsum on expsum.pmainindex = ipinfo.pmainindex  \n ") ;
	    sql.append(" left join drgs.t_drgs drg on pd.pardrg_code = drg.drg_code and drg.ver_code = pd.ver_code  \n ") ;
	    sql.append(" left join drgs.t_department dt on ipinfo.P00300001=dt.dep_code  \n ") ;
	    if("2".equals(compareType)){
	    	sql.append(" left join ( \n" ) ;
			sql.append(" select  pd.pardrg_code, avg("+tip+") stddevia ,(1+"+sliderValBenchLater+"*stddev("+tip+")/100)* avg("+tip+") stdd \n" ) ;
			sql.append(" from drgs.mr_baseinfo a   left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex  \n" ) ;
			sql.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid   \n" ) ;
			sql.append(" left join drgs.t_annualstd ad on ad.as_drg_code=pd.pardrg_code  \n" ) ;
			sql.append(" LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ipinfo.PMAININDEX \n ");
			sql.append(" left join drgs.mr_expense_sum expsum on ipinfo.pmainindex=expsum.pmainindex  \n" ) ;
			sql.append(" where pd.ver_region='"+groupScheme+"'  and  ipinfo.P00300001='"+depCode+"'  \n" ) ;
			//科室维度的钻取
			if("dep".equals(monitorType)){ //科室维度
			}
			if (StringUtils.hasLength(dimensionCode)){		//主诊组维度
				sql.append(" and  ipinfo.P00601001='"+dimensionCode+"'   \n" ) ;
			}
			if (StringUtils.hasLength(drgCode)){
                sql.append(" and pd.pardrg_code='"+drgCode+"'   \n" ) ;
            }
			sql.append(" and ad.as_id='"+stdId+"' "+Arith.mrOutDateSql(dateFrom,dateTo,"ipinfo",1)+" \n" ) ;
			//***医院编码***
			if(StringUtils.hasLength(hospCode)){
				sql.append(" and instr('"+hospCode+"', a.p00010001)>0   \n" ) ;
			}
			//***医保条件***
			if("1".equals(pType)){
				sql.append(" AND  medins.parid is not null  \n ");
			}
			sql.append(" group by pd.pardrg_code ) stddevia on stddevia.pardrg_code=pd.pardrg_code \n" ) ;
	    }
		sql.append(" where pd.ver_region='"+groupScheme+"' and ad.as_id='"+stdId+"' \n" ) ;
		sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"ipinfo",1)+" and  ipinfo.P00300001='"+depCode+"'  \n" ) ;
		if("1".equals(compareType)){ // 1是标杆法
			if("not".equals(abnomalType)){ //区间类型  全部病例 all 未超标 not 超标N以内病例 in 超标N及以上病例 more
				sql.append(" and  "+tip+"<=ad."+compareObj+" \n" ) ;
			}else if("in".equals(abnomalType)){ 
				sql.append(" and  ( ad."+compareObj+"<"+tip+" and "+tip+"<="+sliderValBenchLater+"*ad."+compareObj+" ) \n" ) ;
			}else if("more".equals(abnomalType)){
				sql.append(" and  ( "+tip+" > "+sliderValBenchLater+"*ad."+compareObj+" ) \n" ) ;
			}else{
				sql.append(" and  1=1  \n" ) ;
			}
		}else{   //2是标差法
			if("not".equals(abnomalType)){ //区间类型  全部病例 all 未超标 not 超标N以内病例 in 超标N及以上病例 more
				sql.append(" and  "+tip+"<=stddevia.stddevia \n" ) ;
			}else if("in".equals(abnomalType)){
				sql.append(" and  ( stddevia.stddevia<"+tip+" and "+tip+"<=stddevia.stdd ) \n" ) ;
			}else if("more".equals(abnomalType)){
				sql.append(" and  ( "+tip+" > stddevia.stdd ) \n" ) ;
			}else{
				sql.append(" and  1=1  \n" ) ;
			}
		}
		//科室维度的钻取
		if("dep".equals(monitorType)){ //科室维度
		}
        if (StringUtils.hasLength(dimensionCode)){		//主诊组维度
            sql.append(" and  ipinfo.P00601001='"+dimensionCode+"'   \n" ) ;
        }
        if (StringUtils.hasLength(drgCode)){
            sql.append(" and pd.pardrg_code='"+drgCode+"'   \n" ) ;
        }
		//***医院编码***
		if(StringUtils.hasLength(hospCode)){
			sql.append(" and instr('"+hospCode+"', a.p00010001)>0   \n" ) ;
		}
		//***医保条件***
		if("1".equals(pType)){
			sql.append(" AND  medins.parid is not null  \n ");
		}
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}


    /**
     * 不同特征区间内病例分布情况
     */
    @Override
    public List<Map<String, Object>> getDRGOverFeeCaseShow(IndexCal indexCal){
        String dateFrom = indexCal.getDateFrom()==null?"":indexCal.getDateFrom().toString();
        String dateTo = indexCal.getDateTo()==null?"":indexCal.getDateTo().toString();
        String depCode = indexCal.getPardepCode()==null?"":indexCal.getPardepCode().toString();
        String drgCode = indexCal.getPardrgCode()==null?"":indexCal.getPardrgCode().toString();
        String hospCode = indexCal.getHospCode()==null?"":indexCal.getHospCode().toString();
        String calType = indexCal.getType1()==null?"":indexCal.getType1().toString();
        String sliderValBench = indexCal.getType2()==null?"":indexCal.getType2().toString();
        String stdId = indexCal.getStdId()==null?"":indexCal.getStdId().toString();
        String pType = indexCal.getpType()==null?"":indexCal.getpType().toString();
        String groupScheme = indexCal.getGroupType()==null?"":indexCal.getGroupType().toString();
        String groupName = indexCal.getPargroupName()==null?"":indexCal.getPargroupName().toString();

        List<Map<String, Object>> resultList = new ArrayList<>();
        String compareValue = "";
        String avgValue = "";
        String unit = "";
        String extraDesc = "";

        StringBuilder selectSql = new StringBuilder();
        selectSql.append("SELECT ");
        selectSql.append("  MIN(IPINFO.P00800000) minValue, ");
        selectSql.append("  MAX(IPINFO.P00800000) maxValue, ");
        selectSql.append("  COUNT(*) caseCnt,");
        selectSql.append("  ROUND(AVG(IPINFO.P00800000),2) avgFee, ");
        selectSql.append("  AVG(AD.AS_AVG_FEE) stdFee, ");
        selectSql.append("  AVG(AD.AS_DRG_DRUG_COST) stdDrugFee, ");
        selectSql.append("  AVG(AD.AS_DRG_MED_COST) stdMedFee, ");
        selectSql.append("  AVG(AD.AS_DRG_NURS_COST) stdNursFee, ");
        selectSql.append("  AVG(AD.AS_DRG_TECH_COST) stdTechFee, ");
        selectSql.append("  AVG(AD.AS_DRG_MANAGE_COST) stdManageFee, ");
        selectSql.append("  ROUND(AVG(IPINFO.P00320000),2) avgDays, ");
        selectSql.append("  ROUND(AVG(EXPENSE.DRUG_COST),2) avgDrugFee, ");
        selectSql.append("  ROUND(AVG(EXPENSE.DRUG_COST)*100/AVG(IPINFO.P00800000),2) drugRatio, ");
        selectSql.append("  ROUND(AVG(EXPENSE.MATER_COST),2) avgMaterFee, ");
        selectSql.append("  ROUND(AVG(EXPENSE.MATER_COST)*100/AVG(IPINFO.P00800000),2) materRatio, ");
        selectSql.append("  ROUND(AVG(EXPENSE.DRG_MED_COST),2) avgMedFee, ");
        selectSql.append("  ROUND(AVG(EXPENSE.DRG_NURS_COST),2) avgNursFee, ");
        selectSql.append("  ROUND(AVG(EXPENSE.DRG_MANAGE_COST),2) avgManageFee, ");
        selectSql.append("  ROUND(AVG(EXPENSE.DRG_TECH_COST),2) avgTechFee, ");
        selectSql.append("  ROUND(AVG(EXPENSE.DRUG_COST+EXPENSE.MATER_COST),2) avgDrugMaterFee ");

        StringBuilder fromSql = new StringBuilder();
        fromSql.append("FROM DRGS.MR_IPINFO IPINFO ");
        fromSql.append("  left join DRGS.MR_BASEINFO BASE ON BASE.PMAININDEX=IPINFO.PMAININDEX ");
        fromSql.append("  left join DRGS.MR_EXPENSE_SUM EXPENSE ON EXPENSE.PMAININDEX=IPINFO.PMAININDEX ");
        fromSql.append("  LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ipinfo.PMAININDEX ");
        fromSql.append("  LEFT JOIN DRGS.T_PATIENTDRG PD ON IPINFO.PMAININDEX=PD.PARID ");
        fromSql.append("  LEFT JOIN DRGS.T_ANNUALSTD AD ON PD.PARDRG_CODE=AD.AS_DRG_CODE ");
        fromSql.append("WHERE AD.AS_ID='"+stdId+"' ");
        fromSql.append("AND PD.PARDRG_CODE='"+drgCode+"' ");
		fromSql.append("AND pd.ver_region='"+groupScheme+"' ");
        if(StringUtils.hasLength(hospCode)){
            fromSql.append(" and instr('"+hospCode+"', base.p00010001)>0 ") ;
        }
        if(StringUtils.hasLength(depCode)){
            fromSql.append(" and ipinfo.P00300001='"+depCode+"' ") ;
        }
        if (StringUtils.hasLength(groupName)){		//主诊组
            fromSql.append(" and  ipinfo.P00601001='"+groupName+"'  " ) ;
        }
        //***医保条件***
        if("1".equals(pType)){
            fromSql.append(" AND  medins.parid is not null  ");
        }
        //fromSql.append("AND IPINFO.P00290000  BETWEEN TO_DATE('"+dateFrom+"','yyyy-mm-dd') AND TO_DATE('"+dateTo+"','yyyy-mm-dd') ");
        fromSql.append(Arith.mrOutDateSql(dateFrom,dateTo,"IPINFO",1));

        //划分类型 1是标杆法区间划分 2是标差法区间划分
        if ("1".equals(calType)){
            compareValue = sliderValBench+"*AD.AS_AVG_FEE";
            sliderValBench = (int)(Double.valueOf(sliderValBench)*100-100)+"";
            avgValue = "AD.AS_AVG_FEE";
            unit = "%";
        } else {
            compareValue = jdbcTemplateObj.queryForObject("SELECT (1+"+sliderValBench+"*stddev(IPINFO.P00800000)/100)*avg(IPINFO.P00800000) " + fromSql, String.class);
            avgValue = jdbcTemplateObj.queryForObject("SELECT avg(IPINFO.P00800000) " + fromSql, String.class);
            unit = "倍";
            extraDesc = "标准差";
        }
        Map<String, Object> allMap = null;
        Map<String, Object> overMap = null;
        Map<String, Object> underMap = null;
        Map<String, Object> otherMap = null;

        try {
            //全部病例
            System.out.println(selectSql.toString() + fromSql);
            allMap = jdbcTemplateObj.queryForList(selectSql.toString() + fromSql).get(0);
            allMap.put("ccode", "all");
            allMap.put("ddesc", "全部病例");

            //超标以上病例
            overMap = jdbcTemplateObj.queryForList(selectSql.toString() + fromSql + " AND IPINFO.P00800000>=" + compareValue).get(0);
            overMap.put("ccode", "over");
            overMap.put("ddesc", "超出"+sliderValBench+unit+"以上"+extraDesc+"病例");

            //超标以下病例
            underMap = jdbcTemplateObj.queryForList(selectSql.toString() + fromSql + " AND IPINFO.P00800000>="+avgValue+" AND IPINFO.P00800000<" + compareValue).get(0);
            underMap.put("ccode", "under");
            underMap.put("ddesc", "超出"+sliderValBench+unit+"以内"+extraDesc+"病例");

            //未超标病例
            otherMap = jdbcTemplateObj.queryForList(selectSql.toString() + fromSql + " AND IPINFO.P00800000<"+avgValue).get(0);
            otherMap.put("ccode", "other");
            otherMap.put("ddesc", "未超标病例");

        } catch (DataAccessException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }

        resultList.add(allMap);
        resultList.add(overMap);
        resultList.add(underMap);
        resultList.add(otherMap);
        return resultList;
    }
    /**
     * 数据库现有的绩效值的指标获取
     */
	@Override
	public List<Map<String, Object>> getManagerQuotaCode(String dateTo, String pType) {
		
		//绩效年份选择时间对应的年份，如果没有选择系统最大
		String managerSql =" select distinct t.parquota_code "; //设置绩效值sql
		managerSql += " from drgs.T_QUOTAMANAGE t where t.par_ms_code like 'manager_%' ";
		managerSql += " and t.pardep_code is not null and t.patient_type='"+pType+"' \n ";
		String managerYearSql=" select count(1) from drgs.T_QUOTAMANAGE t where t.par_ms_code like 'manager_%' AND t.manage_year='"+dateTo.substring(0,4)+"' ";
		if(jdbcTemplateObj.queryForObject(managerYearSql.toString(), Integer.class)>1){
			managerSql += " and t.manage_year='"+dateTo.substring(0,4)+"' \n ";
		}else{
			managerSql += " and t.manage_year in ( select max(t.manage_year) from drgs.T_QUOTAMANAGE t where t.par_ms_code like 'manager_%' )   \n ";
		}
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(managerSql.toString()) ;
		return list;
	}
	/**
	 * DRG病组次均费用、费用构成及其对比情况表数据获取
	 */
	@Override
	public List<Map<String, Object>> getDrgCompareData(IndexCal indexCal) {
		
		String dateFrom = indexCal.getDateFrom()==null?"":indexCal.getDateFrom().toString();
        String dateTo = indexCal.getDateTo()==null?"":indexCal.getDateTo().toString();
        String depCode = indexCal.getPardepCode()==null?"":indexCal.getPardepCode().toString();
        String hospCode = indexCal.getHospCode()==null?"":indexCal.getHospCode().toString();
        String stdId = indexCal.getStdId()==null?"":indexCal.getStdId().toString();
        String pType = indexCal.getpType()==null?"":indexCal.getpType().toString();
        String groupScheme = indexCal.getGroupType()==null?"":indexCal.getGroupType().toString();
        String groupName = indexCal.getPargroupName()==null?"":indexCal.getPargroupName().toString();
		String compareType=indexCal.getCompareObj()==null?"":indexCal.getCompareObj().toString(); //超标的指标项
        
		String compareObj="AS_AVG_FEE";	//标杆实际字段
		String tip = "ipinfo.P00800000"; //医院数据字段
		//标杆字段
		if("AvgDays".equals(compareType)){
			compareObj="AS_AVG_DAYS";
			tip="ipinfo.P00320000";
		}else if("AvgFee".equals(compareType)){
			compareObj="AS_AVG_FEE";
			tip="ipinfo.P00800000";
		}else if("MedFee".equals(compareType)){
			compareObj="AS_DRG_MED_COST";
			tip="expsum.DRG_MED_COST";
		}else if("TechFee".equals(compareType)){
			compareObj="AS_DRG_TECH_COST";
			tip="expsum.DRG_TECH_COST";
		}else if("NursFee".equals(compareType)){
			compareObj="AS_DRG_NURS_COST";
			tip="expsum.DRG_NURS_COST";
		}else if("DrugMaterFee".equals(compareType)){
			compareObj="AS_DRG_DRUG_COST";
			tip="expsum.DRG_DRUG_COST";
		}else if("ManageFee".equals(compareType)){
			compareObj="AS_DRG_MANAGE_COST";
			tip="expsum.DRG_MANAGE_COST";
		}
		
		String intervalType="";
		String sliderValBench="";
		String abnomalType="";
        //1表示从费用构成及对比点击进来的，2表示从区间分布点击进来的
        if("2".equals(indexCal.getTypeFour())){
        	  intervalType=indexCal.getCompareType(); //划分类型 1是标杆法区间划分 2是标差法区间划分
        	  sliderValBench = indexCal.getType2(); //sliderValBenchLater 超标系数值
              abnomalType = indexCal.getType3(); //abnomalType 区间类型  全部病例 all 未超标 not 超标N以内病例 in 超标N及以上病例 more
        }
        StringBuilder sql = new StringBuilder();
        sql.append(" \n" ) ;
        sql.append(" select  pd.pardrg_code ccode ,drgs.drg_desc ddesc,  count(1) InCaseCnt \n" ) ;
        sql.append(" ,sum(case when "+tip+" >ad."+compareObj+" then 1 else 0 end ) ExcessCnt_F \n" ) ;
        sql.append(" ,round(decode(COUNT(1),0,0,sum(decode(ad.AS_AVG_FEE,0,0,ipinfo.P00800000/ad.AS_AVG_FEE))/COUNT(1)),2)  FeeEff \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(ipinfo.P00800000)/count(1)),2) AvgFee \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(ipinfo.P00320000)/count(1)),2) AvgDays \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(expsum.drug_cost)/count(1)),2) AvgDrugFee  \n" ) ;
        sql.append(" ,round(decode(sum(ipinfo.P00800000),0,0,sum(expsum.drug_cost)/sum(ipinfo.P00800000))*100,2) DrugRatio \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(expsum.mater_cost)/count(1)),2) AvgMateFee \n" ) ;
        sql.append(" ,round(decode(sum(ipinfo.P00800000),0,0,sum(expsum.mater_cost)/sum(ipinfo.P00800000))*100,2) MaterialRatio \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Med_Cost)/count(1)),2) MedFee \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Tech_Cost)/count(1)),2) TechFee  \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Nurs_Cost)/count(1)),2) NursFee \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Drug_Cost)/count(1)),2) DrugMaterFee  \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Manage_Cost)/count(1)),2) ManageFee  \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(ad.as_avg_fee)/count(1)),2) AvgFeecc  \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(ad.as_avg_days)/count(1)),2) AvgDayscc  \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(ad.as_avg_drug_fee)/count(1)),2) AvgDrugFeecc  \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(ad.as_drug_ratio)/count(1)),2) DrugRatiocc  \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(ad.as_avg_material_fee)/count(1)),2) AvgMateFeecc  \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(ad.as_material_ratio)/count(1)),2) MaterialRatiocc  \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(ad.as_drg_med_cost)/count(1)),2) MedFeecc  \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(ad.as_drg_tech_cost)/count(1)),2) TechFeecc \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(ad.as_drg_nurs_cost)/count(1)),2) NursFeecc  \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(ad.as_drg_drug_cost)/count(1)),2) DrugMaterFeecc  \n" ) ;
        sql.append(" ,round(decode(count(1),0,0,sum(ad.as_drg_manage_cost)/count(1)),2) ManageFeecc  \n" ) ;
        sql.append(" from drgs.mr_baseinfo a   \n" ) ;
        sql.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex  \n" ) ;
		sql.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid and  pd.ver_region='"+groupScheme+"'  \n" ) ;
		sql.append(" left join drgs.t_drgs drgs on drgs.drg_code=pd.pardrg_code and drgs.ver_code=pd.ver_code \n" ) ;
		sql.append(" left join drgs.mr_expense_sum expsum on ipinfo.pmainindex=expsum.pmainindex   \n" ) ;
		sql.append(" left join drgs.t_annualstd ad on ad.as_drg_code=pd.pardrg_code and ad.as_id='"+stdId+"' \n" ) ;
		sql.append(" LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ipinfo.PMAININDEX \n" ) ;
		//求标差法的标杆值
		if("2".equals(indexCal.getTypeFour())&&"2".equals(intervalType)){
			sql.append(" left join ( \n" ) ;
			sql.append(" select  pd.pardrg_code, avg("+tip+") stddevia ,(1+"+sliderValBench+"*stddev("+tip+")/100)* avg("+tip+") stdd \n" ) ;
			sql.append(" from drgs.mr_baseinfo a   left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex  \n" ) ;
			sql.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid   \n" ) ;
			sql.append(" left join drgs.t_annualstd ad on ad.as_drg_code=pd.pardrg_code  \n" ) ;
			sql.append(" LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ipinfo.PMAININDEX \n ");
			sql.append(" left join drgs.mr_expense_sum expsum on ipinfo.pmainindex=expsum.pmainindex  \n" ) ;
			sql.append(" where pd.ver_region='"+groupScheme+"' and  ipinfo.P00300001='"+depCode+"'  \n" ) ;
			sql.append(" and ad.as_id='"+stdId+"' "+Arith.mrOutDateSql(dateFrom,dateTo,"ipinfo",1)+" \n" ) ;
			//***医院编码***
			if(StringUtils.hasLength(hospCode)){
				sql.append(" and instr('"+hospCode+"', a.p00010001)>0   \n" ) ;
			}
			//***主诊组编码***
			if(StringUtils.hasLength(groupName)){
				sql.append(" and ipinfo.P00601001='"+groupName+"'  \n" ) ;
			}
			//***医保条件***
			if("1".equals(pType)){
				sql.append(" AND  medins.parid is not null  \n ");
			}
			sql.append(" group by pd.pardrg_code ) stddevia on stddevia.pardrg_code=pd.pardrg_code \n" ) ;
		}
		sql.append(" where pd.pardrg_code is not null  \n" ) ;
	 	if(StringUtils.hasLength(hospCode)){	//全院
	 		sql.append(" and instr('"+hospCode+"', a.p00010001)>0 ") ;
        }
        if(StringUtils.hasLength(depCode)){	//科室
        	sql.append(" and ipinfo.P00300001='"+depCode+"' ") ;
        }
        if (StringUtils.hasLength(groupName)){	//主诊组
        	sql.append(" and  ipinfo.P00601001='"+groupName+"'  " ) ;
        }
        //***医保条件***
        if("1".equals(pType)){
        	sql.append(" AND  medins.parid is not null  ");
        }
		sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"ipinfo",1)+" \n" ) ;
		//1表示从费用构成及对比点击进来的，2表示从区间分布点击进来的
        if("2".equals(indexCal.getTypeFour())){
        	if("1".equals(intervalType)){ // 1是标杆法
    			if("not".equals(abnomalType)){ //区间类型  全部病例 all 未超标 not 超标N以内病例 in 超标N及以上病例 more
    				sql.append(" and  "+tip+"<=ad."+compareObj+" \n" ) ;
    			}else if("in".equals(abnomalType)){ 
    				sql.append(" and  ( ad."+compareObj+"<"+tip+" and "+tip+"<="+sliderValBench+"*ad."+compareObj+" ) \n" ) ;
    			}else if("more".equals(abnomalType)){
    				sql.append(" and  ( "+tip+" > "+sliderValBench+"*ad."+compareObj+" ) \n" ) ;
    			}else{
    				sql.append(" and  1=1  \n" ) ;
    			}
    		}else{   //2是标差法
    			if("not".equals(intervalType)){ //区间类型  全部病例 all 未超标 not 超标N以内病例 in 超标N及以上病例 more
    				sql.append(" and  "+tip+"<=stddevia.stddevia \n" ) ;
    			}else if("in".equals(abnomalType)){
    				sql.append(" and  ( stddevia.stddevia<"+tip+" and "+tip+"<=stddevia.stdd ) \n" ) ;
    			}else if("more".equals(abnomalType)){
    				sql.append(" and  ( "+tip+" > stddevia.stdd ) \n" ) ;
    			}else{
    				sql.append(" and  1=1  \n" ) ;
    			}
    		}
        }
        sql.append(" group by pd.pardrg_code ,drgs.drg_desc order by count(1) desc \n" ) ;
        
        List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 * 病组展开的 次均费用、费用构成及其对比情况表
	 * @param indexCal
	 * @return
	 */
	@Override
	public List<Map<String, Object>> getDrgFeeCompareTable(IndexCal indexCal) {
		String dateFrom = indexCal.getDateFrom()==null?"":indexCal.getDateFrom().toString();
        String dateTo = indexCal.getDateTo()==null?"":indexCal.getDateTo().toString();
        String depCode = indexCal.getPardepCode()==null?"":indexCal.getPardepCode().toString();
        String hospCode = indexCal.getHospCode()==null?"":indexCal.getHospCode().toString();
        String stdId = indexCal.getStdId()==null?"":indexCal.getStdId().toString();
        String pType = indexCal.getpType()==null?"":indexCal.getpType().toString();
        String groupScheme = indexCal.getGroupType()==null?"":indexCal.getGroupType().toString();
        String groupName = indexCal.getPargroupName()==null?"":indexCal.getPargroupName().toString();
        String drgCode=indexCal.getPardrgCode()==null?"":indexCal.getPardrgCode().toString();
		
        int thisYear = Integer.parseInt(dateFrom.substring(0,4));//当年
    	String monthFrom=dateFrom.substring(5);	//起始月份
		String monthTo=dateTo.substring(5);	//起始月份
		
		String compareType=indexCal.getCompareObj(); //超标的指标项
		String compareObj="AS_AVG_FEE";	//标杆实际字段
		String tip = "ipinfo.P00800000"; //医院数据字段
		//标杆字段
		if("AvgDays".equals(compareType)){
			compareObj="AS_AVG_DAYS";
			tip="ipinfo.P00320000";
		}else if("AvgFee".equals(compareType)){
			compareObj="AS_AVG_FEE";
			tip="ipinfo.P00800000";
		}else if("MedFee".equals(compareType)){
			compareObj="AS_DRG_MED_COST";
			tip="expsum.DRG_MED_COST";
		}else if("TechFee".equals(compareType)){
			compareObj="AS_DRG_TECH_COST";
			tip="expsum.DRG_TECH_COST";
		}else if("NursFee".equals(compareType)){
			compareObj="AS_DRG_NURS_COST";
			tip="expsum.DRG_NURS_COST";
		}else if("DrugMaterFee".equals(compareType)){
			compareObj="AS_DRG_DRUG_COST";
			tip="expsum.DRG_DRUG_COST";
		}else if("ManageFee".equals(compareType)){
			compareObj="AS_DRG_MANAGE_COST";
			tip="expsum.DRG_MANAGE_COST";
		}
		
		String intervalType="";
		String sliderValBench="";
		String abnomalType="";
        //1表示从费用构成及对比点击进来的，2表示从区间分布点击进来的
        if("2".equals(indexCal.getTypeFour())){
        	  intervalType=indexCal.getCompareType(); //划分类型 1是标杆法区间划分 2是标差法区间划分
        	  sliderValBench = indexCal.getType2(); //sliderValBenchLater 超标系数值
              abnomalType = indexCal.getType3(); //abnomalType 区间类型  全部病例 all 未超标 not 超标N以内病例 in 超标N及以上病例 more
        }
		
		//环比的时间值
		String dateFromCc = DrgsIndex.getDateFromCc(dateFrom,"month");
		String dateToCc = DrgsIndex.getDateToCc(dateTo,"month");
		
        //指标查询sql拼接
        String quotaSql=" ";
        quotaSql += "  \n ";
        quotaSql += " ,round(decode(count(1),0,0,sum(ipinfo.P00800000)/count(1)),2) AvgFee \n ";
        quotaSql += " ,round(decode(count(1),0,0,sum(ipinfo.P00320000)/count(1)),2) AvgDays \n ";
        quotaSql += " ,round(decode(count(1),0,0,sum(expsum.drug_cost)/count(1)),2) AvgDrugFee \n ";
        quotaSql += " ,round(decode(sum(ipinfo.P00800000),0,0,sum(expsum.drug_cost)/sum(ipinfo.P00800000))*100,2) DrugRatio \n ";
        quotaSql += " ,round(decode(count(1),0,0,sum(expsum.mater_cost)/count(1)),2) AvgMateFee \n ";
        quotaSql += " ,round(decode(sum(ipinfo.P00800000),0,0,sum(expsum.mater_cost)/sum(ipinfo.P00800000))*100,2) MaterialRatio \n ";
        quotaSql += " ,round(decode(count(1),0,0,sum(expsum.Drg_Med_Cost)/count(1)),2) MedFee \n ";
        quotaSql += " ,round(decode(count(1),0,0,sum(expsum.Drg_Tech_Cost)/count(1)),2) TechFee  \n ";
        quotaSql += " ,round(decode(count(1),0,0,sum(expsum.Drg_Nurs_Cost)/count(1)),2) NursFee  \n ";
        quotaSql += ",round(decode(count(1),0,0,sum(expsum.Drg_Drug_Cost)/count(1)),2) DrugMaterFee \n ";
        quotaSql += " ,round(decode(count(1),0,0,sum(expsum.Drg_Manage_Cost)/count(1)),2) ManageFee \n ";
        quotaSql += " from drgs.mr_baseinfo a   \n ";
        quotaSql += " left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex  \n ";
		quotaSql += " left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid and  pd.ver_region='"+groupScheme+"'  \n ";
		quotaSql += " left join drgs.mr_expense_sum expsum on ipinfo.pmainindex=expsum.pmainindex  \n "; 
		quotaSql += " left join drgs.t_annualstd ad on ad.as_drg_code=pd.pardrg_code and ad.as_id='"+stdId+"' \n ";
		quotaSql += " LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ipinfo.PMAININDEX \n ";
		quotaSql += " where pd.pardrg_code is not null  and ad.as_drg_code='"+drgCode+"' \n ";
		if(StringUtils.hasLength(hospCode)){	//全院
			quotaSql += " and instr('"+hospCode+"', a.p00010001)>0  \n ";
        }
        //***医保条件***
        if("1".equals(pType)){
        	quotaSql += " AND  medins.parid is not null \n";
        }
        
        StringBuilder sql = new StringBuilder();
        sql.append(" \n" ) ;
        sql.append(" (select  'std' ccode ,'标杆值（该DRG病组）' ddesc,  ad.as_avg_fee AvgFee ,ad.as_avg_days AvgDays ,ad.as_avg_drug_fee AvgDrugFee \n" ) ;
        sql.append(" ,ad.as_drug_ratio DrugRatio  ,ad.as_avg_material_fee AvgMateFee  ,ad.as_material_ratio MaterialRatio  ,ad.as_drg_med_cost MedFee \n" ) ;
        sql.append(" ,ad.as_drg_tech_cost TechFee ,ad.as_drg_nurs_cost NursFee ,ad.as_drg_drug_cost DrugMaterFee ,ad.as_drg_manage_cost ManageFee \n" ) ;
        sql.append(" from drgs.t_annualstd ad where ad.as_id='"+stdId+"' and ad.as_drg_code='"+drgCode+"')  \n" ) ;
        if("1".equals(indexCal.getTypeFour())){
        	sql.append(" UNION (select  'this' ccode ,'本期值' ddesc \n" ) ;
            sql.append(quotaSql) ;
            if(StringUtils.hasLength(depCode)){	//科室
            	sql.append(" and ipinfo.P00300001='"+depCode+"' ") ;
            }
            if (StringUtils.hasLength(groupName)){	//主诊组
            	sql.append(" and  ipinfo.P00601001='"+groupName+"'  " ) ;
            }
            sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"ipinfo",1)+" ) \n" ) ;
        }
        sql.append(" UNION (select 'avgdep' ccode ,'平均值（本科室该DRG病组）' ddesc \n" ) ;
        sql.append(quotaSql) ;
        if(StringUtils.hasLength(depCode)){	//科室
        	sql.append(" and ipinfo.P00300001='"+depCode+"' ") ;
        }
        sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"ipinfo",1)+" \n" ) ;
        sql.append(" ) UNION (select 'avghosp' ccode ,'平均值（全院该DRG病组）' ddesc \n" ) ; 
        sql.append(quotaSql) ;
        sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"ipinfo",1)+" ) \n" ) ;
        //1是对比情况表点击进来的 2是区间分布点击进来的，获取的行项不一样
        if("1".equals(indexCal.getTypeFour())){
        	sql.append("  UNION (select  'thisno' ccode ,'上期值' ddesc \n" ) ;
            sql.append(quotaSql) ;
            if(StringUtils.hasLength(depCode)){	//科室
            	sql.append(" and ipinfo.P00300001='"+depCode+"' ") ;
            }
            if (StringUtils.hasLength(groupName)){	//主诊组
            	sql.append(" and  ipinfo.P00601001='"+groupName+"'  " ) ;
            }
            //上期值
            sql.append(" and to_char(ipinfo.P00290000,'yyyy-mm-dd') between '"+dateFromCc+"' and '"+dateToCc+"' ) \n" ) ;   
            sql.append(" UNION (select  'last' ccode ,'同期值' ddesc \n" ) ; 
            sql.append(quotaSql) ;
            if(StringUtils.hasLength(depCode)){	//科室
            	sql.append(" and ipinfo.P00300001='"+depCode+"' ") ;
            }
            if (StringUtils.hasLength(groupName)){	//主诊组
            	sql.append(" and  ipinfo.P00601001='"+groupName+"'  " ) ;
            }
            //同期值
            sql.append(" and to_char(ipinfo.P00290000,'yyyy-mm-dd') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"' ) \n" ) ; 
        }else{
        	 sql.append(" UNION (select 'avgInterval' ccode ,'平均值（区间内该DRG病组）' ddesc \n" ) ; 
        	 sql.append(" ,round(decode(count(1),0,0,sum(ipinfo.P00800000)/count(1)),2) AvgFee \n" ) ; 
        	 sql.append(" ,round(decode(count(1),0,0,sum(ipinfo.P00320000)/count(1)),2) AvgDays \n" ) ; 
        	 sql.append(" ,round(decode(count(1),0,0,sum(expsum.drug_cost)/count(1)),2) AvgDrugFee \n" ) ; 
        	 sql.append(" ,round(decode(sum(ipinfo.P00800000),0,0,sum(expsum.drug_cost)/sum(ipinfo.P00800000))*100,2) DrugRatio \n" ) ; 
        	 sql.append(" ,round(decode(count(1),0,0,sum(expsum.mater_cost)/count(1)),2) AvgMateFee \n" ) ; 
        	 sql.append(" ,round(decode(sum(ipinfo.P00800000),0,0,sum(expsum.mater_cost)/sum(ipinfo.P00800000))*100,2) MaterialRatio \n" ) ; 
        	 sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Med_Cost)/count(1)),2) MedFee \n" ) ; 
        	 sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Tech_Cost)/count(1)),2) TechFee  \n" ) ; 
        	 sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Nurs_Cost)/count(1)),2) NursFee  \n" ) ; 
        	 sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Drug_Cost)/count(1)),2) DrugMaterFee \n" ) ; 
        	 sql.append(" ,round(decode(count(1),0,0,sum(expsum.Drg_Manage_Cost)/count(1)),2) ManageFee \n" ) ; 
        	 sql.append(" from drgs.mr_baseinfo a  \n" ) ; 
        	 sql.append(" left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex  \n" ) ; 
        	 sql.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid and  pd.ver_region='"+groupScheme+"' \n" ) ; 
        	 sql.append(" left join drgs.mr_expense_sum expsum on ipinfo.pmainindex=expsum.pmainindex  \n" ) ; 
        	 sql.append(" left join drgs.t_annualstd ad on ad.as_drg_code=pd.pardrg_code and ad.as_id='"+stdId+"' \n" ) ; 
        	 sql.append(" LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ipinfo.PMAININDEX \n" ) ; 
    		 sql.append(" left join ( \n" ) ;
			 sql.append(" select  pd.pardrg_code, avg("+tip+") stddevia ,(1+"+sliderValBench+"*stddev("+tip+")/100)* avg("+tip+") stdd \n" ) ;
			 sql.append(" from drgs.mr_baseinfo a   left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex  \n" ) ;
		 	 sql.append(" left join drgs.t_patientdrg pd on ipinfo.pmainindex = pd.parid   \n" ) ;
			 sql.append(" left join drgs.t_annualstd ad on ad.as_drg_code=pd.pardrg_code  \n" ) ;
			 sql.append(" LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ipinfo.PMAININDEX \n ");
			 sql.append(" left join drgs.mr_expense_sum expsum on ipinfo.pmainindex=expsum.pmainindex  \n" ) ;
			 sql.append(" where pd.ver_region='"+groupScheme+"' and  ipinfo.P00300001='"+depCode+"'  \n" ) ;
			 sql.append(" and ad.as_id='"+stdId+"' "+Arith.mrOutDateSql(dateFrom,dateTo,"ipinfo",1)+" \n" ) ;
			 //***医院编码***
			 if(StringUtils.hasLength(hospCode)){
				sql.append(" and instr('"+hospCode+"', a.p00010001)>0   \n" ) ;
			 }
			 //***主诊组编码***
			 if(StringUtils.hasLength(groupName)){
				sql.append(" and ipinfo.P00601001='"+groupName+"'  \n" ) ;
			 }
			 //***医保条件***
			 if("1".equals(pType)){
				sql.append(" AND  medins.parid is not null  \n ");
			 }
			 sql.append(" group by pd.pardrg_code ) stddevia on stddevia.pardrg_code=pd.pardrg_code \n" ) ;
			 sql.append(" where pd.pardrg_code is not null  and ad.as_drg_code='"+drgCode+"' \n ");
			 if(StringUtils.hasLength(hospCode)){	//全院
				sql.append(" and instr('"+hospCode+"', a.p00010001)>0  \n ");
	         }
	         //***医保条件***
	         if("1".equals(pType)){
	        	sql.append(" AND  medins.parid is not null \n ");
	         }
        	 if(StringUtils.hasLength(depCode)){	//科室
             	sql.append(" and ipinfo.P00300001='"+depCode+"' ") ;
             }
             if (StringUtils.hasLength(groupName)){	//主诊组
             	sql.append(" and  ipinfo.P00601001='"+groupName+"'  " ) ;
             }
             if("1".equals(intervalType)){ // 1是标杆法
     			if("not".equals(abnomalType)){ //区间类型  全部病例 all 未超标 not 超标N以内病例 in 超标N及以上病例 more
     				sql.append(" and  "+tip+"<=ad."+compareObj+" \n" ) ;
     			}else if("in".equals(abnomalType)){ 
     				sql.append(" and  ( ad."+compareObj+"<"+tip+" and "+tip+"<="+sliderValBench+"*ad."+compareObj+" ) \n" ) ;
     			}else if("more".equals(abnomalType)){
     				sql.append(" and  ( "+tip+" > "+sliderValBench+"*ad."+compareObj+" ) \n" ) ;
     			}else{
     				sql.append(" and  1=1  \n" ) ;
     			}
     		}else{   //2是标差法
     			if("not".equals(intervalType)){ //区间类型  全部病例 all 未超标 not 超标N以内病例 in 超标N及以上病例 more
     				sql.append(" and  "+tip+"<=stddevia.stddevia \n" ) ;
     			}else if("in".equals(abnomalType)){
     				sql.append(" and  ( stddevia.stddevia<"+tip+" and "+tip+"<=stddevia.stdd ) \n" ) ;
     			}else if("more".equals(abnomalType)){
     				sql.append(" and  ( "+tip+" > stddevia.stdd ) \n" ) ;
     			}else{
     				sql.append(" and  1=1  \n" ) ;
     			}
     		}
	        sql.append(" "+Arith.mrOutDateSql(dateFrom,dateTo,"ipinfo",1)+" ) \n" ) ;
        }
        
        List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
}
